Working with Custom Tables in WordPress – Introduction

When working on custom solutions or plugins in WordPress, custom tables can be a good solution because it will give you better performance than by using the default tables such as posts or postmeta.

Some popular plugins such as WooCommerce and Easy Digital Downloads also use custom tables. Easy Digital Downloads is moving into a complete custom table architecture so it will become much faster.

In 2018, WooCommerce tried loading a big database to compare how the site would perform if they move products to custom tables. They’ve found out that there was a 30% increase in performance (faster page loads).

When starting a plugin, many are using the default posts table because it gives a whole CRUD functionality, layout and even, on the front, you get your routes and rewrite rules done for you. If you ask me, that’s a great way to start and test your idea with a plugin.

But later on, you should definitely move on your custom tables if you plan to store different data so you free up the posts and postmeta tables.

WordPress Default Tables

Here are all the default tables that you can use in WordPress (wp_ is just a prefix):

• wp_options
• wp_users
• wp_links
• wp_commentmeta
• wp_term_relationships
• wp_postmeta
• wp_posts
• wp_term_taxonomy
• wp_usermeta
• wp_terms
• wp_termsmeta
• wp_comments

Tables posts and postmeta are the most used in general since those are used for creating posts, pages, attachments and for all other custom post types.

Other tables that can be used are all regarding taxonomies. Those are structured much better. The architecture of those tables can be checked and used for making other relationship tables.

WooCommerce Tables

Since I work a lot with WooCommerce, I’ll present their solution. At the time of writing this, they had these tables:

• Table: woocommerce_sessions
• Table: woocommerce_api_keys
• Table: woocommerce_attribute_taxonomies
• Table: woocommerce_downloadable_product_permissions
• Table: woocommerce_order_items
• Table: woocommerce_order_itemmeta
• Table: woocommerce_tax_rates
• Table: woocommerce_tax_rate_locations
• Table: woocommerce_shipping_zones
• Table: woocommerce_shipping_zone_locations
• Table: woocommerce_shipping_zone_methods
• Table: woocommerce_payment_tokens
• Table: woocommerce_payment_tokenmeta
• Table: woocommerce_log
• Table: wc_webhooks
• Table: wc_download_log
• Table: wc_product_meta_lookup
• Table: wc_tax_rate_classes

As you can see, they rely on a lot of custom tables. Clicking on each of them above will lead you to their table definition.

If you look closely at the above list, you’ll notice there are 2 tables with a meta suffix. WordPress has metadata functions that can be used to work on such tables.

When working with custom tables, you’ll have to work directly with $wpdb.

Working with Meta Tables

If you have a data type that might also have some other meta tables, you’ll have to register your meta table. So, how does it work?

Meta Table definition: Name {data_type}meta

This table is used for everything meta table in WordPress. Of course, you could have more columns if you need them, but those are the columns that you need for the WordPress meta functions to work.

The only thing that is unique, it is the {data_type}. So you need to have the same table name as well. In case of WooCommerce, you’ll have the meta table for data type order_item:

• Table: woocommerce_order_itemmeta
• Type: order_item

You still can’t use the add_metadata and similar meta functions. Your table has to be registered inside of $wpdb.

So, you’ll have to register it such as $wpdb->order_itemmeta = $wpdb->prefix . 'woocommerce_order_itemmeta'. That way, if we use add_metadata( 'order_item', ... ), WordPress will look for a table registered as order_itemmeta and use that for CRUD operations.

Installing Custom Tables

When should you install the custom tables in WordPress?

• on plugin activation,
• on update flow

In most cases, you’ll install custom tables on plugin activation using the method register_activation_hook .

<?php

register_activation_hook( __FILE__, 'function_to_install_db' );

Update Flow

If you work with custom tables, you should definitely have an update flow. We will cover that in one of the tutorials of this series. But to give you an overview:

• On Page load, get your plugin version from DB
• Check it against the current plugin version
• If the version from DB is older, start an update
• Inside of that update, run all update functions up to the current version and update the version in DB

Creating Tables

To create tables, with WordPress, you have the function dbDelta. You can pass a string containing multiple table schemas and it will create them.

Another pretty useful thing is that dbDelta will alter the tables if they are already there, so you don’t have to worry about update queries.

<?php

function function_to_install_db() {
  global $wpdb;
  
  // Let's not break the site with exception messages
  $wpdb->hide_errors();
  
  if ( ! function_exists( 'dbDelta' ) ) {
    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
  }

  $collate = '';

  if ( $wpdb->has_cap( 'collation' ) ) {
	$collate = $wpdb->get_charset_collate();
  }
  
  $schema = "
CREATE TABLE {$wpdb->prefix}your_table (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  -- Add columns here
  PRIMARY KEY  (id)
) $collate;";

  dbDelta( $schema );
}

Conclusion

Custom Tables in WordPress should not be ignored even though you don’t have a WordPress API for it. As with any other application, you would create custom tables for your needs and not stuff everything into two tables.

But you have to be aware when to install them, when to update and when to delete them.

I’ll go over various parts of working with custom tables in the next few tutorial series so stay tuned.